--them cot ContNo varchar(50)
IF NOT EXISTS ( SELECT  *
            FROM    syscolumns
            WHERE   id = OBJECT_ID('tblCont')
                    AND name = 'ContNo' ) 
	ALTER TABLE tblCont ADD ContNo VARCHAR(50) NULL
go
--them cot ID_ContType int
IF NOT EXISTS ( SELECT  *
            FROM    syscolumns
            WHERE   id = OBJECT_ID('tblCont')
                    AND name = 'ID_ContType' ) 
	ALTER TABLE tblCont ADD ID_ContType int NULL
go
---cap nhat du lieu cho ContNo va ConType
update tblCont 
set tblCont.ContNo = tblLineCont.ContNo, tblCont.ID_ContType = tblLineCont.ID_ContType
from tblCont inner join tblLineCont on tblCont.ID_LineCont = tblLineCont.ID_LineCont
go
---bo khoa ngoai ID_LineCont
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_tblCont_tblLineCont]') AND      
parent_object_id = OBJECT_ID('tblCont'))
alter table tblCont drop constraint FK_tblCont_tblLineCont
go
---bo cot LineCont
IF EXISTS ( SELECT  *
            FROM    syscolumns
            WHERE   id = OBJECT_ID('tblCont')
                    AND name = 'ID_LineCont' ) 
	ALTER TABLE tblCont DROP COLUMN ID_LineCont
go
-----them khoa ngoai voi bang tblContType
if not exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_tblCont_tblContType]') AND      
parent_object_id = OBJECT_ID('tblCont'))
		ALTER TABLE tblCont
		ADD CONSTRAINT FK_tblCont_tblContType
		FOREIGN KEY (ID_ContType)
		REFERENCES tblContType(ID_ContType)
go

